﻿

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using MySql.Data.MySqlClient;

public class DAL_tbl_users
{
    public DAL_tbl_users()
    {

    }



    public List<tbl_users> select(int USER_CODE)
    {
        List<tbl_users> list = new List<tbl_users>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select USER_CODE,USER_NAME,USER_PASSWORD,USER_TYPE_ID,IS_ACTIVE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE from tbl_users where USER_CODE=@USER_CODE";
        cmd.Parameters.AddWithValue("@USER_CODE", USER_CODE);
        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_users()
            {
                USER_CODE = Convert.ToInt32(dr["USER_CODE"]),
                USER_NAME = dr["USER_NAME"].ToString(),
                USER_PASSWORD = dr["USER_PASSWORD"].ToString(),
                USER_TYPE_ID = Convert.ToInt32(dr["USER_TYPE_ID"]),
                IS_ACTIVE = Convert.ToBoolean(dr["IS_ACTIVE"]),
                CREATED_BY = dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
                UPDATED_BY = dr["UPDATED_BY"].ToString(),
                UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError()
            });
        }

        return list;
    }



    public List<tbl_users> selectAll()
    {
        List<tbl_users> list = new List<tbl_users>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select USER_CODE,USER_NAME,USER_PASSWORD,USER_TYPE_ID,IS_ACTIVE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE from tbl_users Order By USER_CODE";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_users()
            {
                USER_CODE = Convert.ToInt32(dr["USER_CODE"]),
                USER_NAME = dr["USER_NAME"].ToString(),
                USER_PASSWORD = dr["USER_PASSWORD"].ToString(),
                USER_TYPE_ID = Convert.ToInt32(dr["USER_TYPE_ID"]),
                IS_ACTIVE = Convert.ToBoolean(dr["IS_ACTIVE"]),
                CREATED_BY = dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
                UPDATED_BY = dr["UPDATED_BY"].ToString(),
                UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError()
            });
        }

        return list;
    }

    public DataTable selectAllForGrid()
    {
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = @"Select u.USER_CODE,u.USER_NAME,u.USER_PASSWORD,ut.USER_TYPE as USER_TYPE_ID,CASE WHEN u.IS_ACTIVE =1 THEN 'True' ELSE 'False' END as 'IS_ACTIVE',u.CREATED_BY,u.CREATED_DATE,u.UPDATED_BY,u.UPDATED_DATE 
from kanyogendra_inventory.tbl_users u LEFT JOIN kanyogendra_inventory.tbl_user_type ut 
on ut.USER_TYPE_ID=u.USER_TYPE_ID Order By u.USER_CODE desc";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        return dt;
    }

    public int insert(int USER_CODE, String USER_NAME, String USER_PASSWORD, int USER_TYPE_ID, bool IS_ACTIVE, String CREATED_BY, DateTime CREATED_DATE)
    {
        int i = 0;
        string sql = @"Insert into tbl_users(USER_CODE, USER_NAME, USER_PASSWORD, USER_TYPE_ID, IS_ACTIVE, CREATED_BY, CREATED_DATE)
                        values(@USER_CODE, @USER_NAME, @USER_PASSWORD, @USER_TYPE_ID, @IS_ACTIVE, @CREATED_BY, @CREATED_DATE)";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@USER_CODE", USER_CODE);
        cmd.Parameters.AddWithValue("@USER_NAME", USER_NAME);
        cmd.Parameters.AddWithValue("@USER_PASSWORD", USER_PASSWORD);
        cmd.Parameters.AddWithValue("@USER_TYPE_ID", USER_TYPE_ID);
        cmd.Parameters.AddWithValue("@IS_ACTIVE", IS_ACTIVE);
        cmd.Parameters.AddWithValue("@CREATED_BY", CREATED_BY);
        cmd.Parameters.AddWithValue("@CREATED_DATE", CREATED_DATE);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



    public int update(int USER_CODE, String USER_NAME, String USER_PASSWORD, int USER_TYPE_ID, bool IS_ACTIVE, String UPDATED_BY, DateTime UPDATED_DATE)
    {
        int i = 0;
        string sql = @"Update tbl_users SET USER_CODE=@USER_CODE, USER_NAME=@USER_NAME, USER_PASSWORD=@USER_PASSWORD, USER_TYPE_ID=@USER_TYPE_ID, IS_ACTIVE=@IS_ACTIVE, UPDATED_BY=@UPDATED_BY, UPDATED_DATE=@UPDATED_DATE WHERE USER_CODE=@USER_CODE";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@USER_CODE", USER_CODE);
        cmd.Parameters.AddWithValue("@USER_NAME", USER_NAME);
        cmd.Parameters.AddWithValue("@USER_PASSWORD", USER_PASSWORD);
        cmd.Parameters.AddWithValue("@USER_TYPE_ID", USER_TYPE_ID);
        cmd.Parameters.AddWithValue("@IS_ACTIVE", IS_ACTIVE);
        cmd.Parameters.AddWithValue("@UPDATED_BY", UPDATED_BY);
        cmd.Parameters.AddWithValue("@UPDATED_DATE", UPDATED_DATE);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }


    public int changePassword(String USER_NAME, string USER_OLD_PASSWORD, String USER_NEW_PASSWORD, String UPDATED_BY, DateTime UPDATED_DATE)
    {
        int i = 0;
        string sql = @"Update tbl_users SET USER_PASSWORD=@USER_NEW_PASSWORD, UPDATED_BY=@UPDATED_BY, UPDATED_DATE=@UPDATED_DATE WHERE USER_NAME=@USER_NAME AND USER_PASSWORD=@USER_OLD_PASSWORD";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@USER_NAME", USER_NAME);
        cmd.Parameters.AddWithValue("@USER_OLD_PASSWORD", USER_OLD_PASSWORD);
        cmd.Parameters.AddWithValue("@USER_NEW_PASSWORD", USER_NEW_PASSWORD);
        cmd.Parameters.AddWithValue("@UPDATED_BY", UPDATED_BY);
        cmd.Parameters.AddWithValue("@UPDATED_DATE", UPDATED_DATE);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }


    public int validateUser(String USER_NAME, String USER_PASSWORD)
    {
        int i = 0;
        string sql = @"select count(1) from tbl_users WHERE USER_NAME=@USER_NAME AND USER_PASSWORD=@USER_PASSWORD";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@USER_NAME", USER_NAME);
        cmd.Parameters.AddWithValue("@USER_PASSWORD", USER_PASSWORD);

        i = Convert.ToInt32(DBUtil.ExecuteScalar(cmd));
        return i;
    }



    public int delete(int USER_CODE)
    {
        int i = 0;
        string sql = "Delete FROM tbl_users WHERE USER_CODE=@USER_CODE";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@USER_CODE", USER_CODE);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



}